# knowledge/database/db_manager.py

import sqlite3
import os
import logging
import json
from datetime import datetime, timedelta
from typing import List, Dict, Optional

# 导入工具模块
import sys
import os
current_dir = os.path.dirname(os.path.abspath(__file__))
util_dir = os.path.join(os.path.dirname(os.path.dirname(current_dir)), "util")
if util_dir not in sys.path:
    sys.path.append(util_dir)
from knowledge.util.tools import logging_helper

class DatabaseManager:
    """数据库管理器"""
    
    def __init__(self, db_path=None, verbose=False):
        self.logger = logging.getLogger("KnowledgeBase.database")
        self.verbose = verbose  # 添加verbose属性
        
        # 定义表描述信息（合并描述和业务含义）
        self.table_descriptions = {
            'employees': '员工信息表 - 存储公司员工基本信息，包括联系方式、部门和职位',
            'products': '产品信息表 - 存储公司产品和服务信息，包括产品描述和负责人', 
            'pipline': '商机表 - 记录销售机会和潜在业务的全过程管理，跟踪从跟进到赢单/丢单的完整生命周期',
            'projects': '项目表 - 记录已签约项目的执行情况，包括合同金额、项目经理和项目状态',
            'projects_products': '项目产品关联表 - 记录项目中销售的具体产品信息，包括销售金额和实施成本'
        }
        
        if db_path is None:
            current_dir = os.path.dirname(os.path.abspath(__file__))
            kb_root = os.path.dirname(os.path.dirname(current_dir))
            db_path = os.path.join(kb_root, "database", "enterprise_data.db")
        
        # 确保数据库目录存在
        db_dir = os.path.dirname(db_path)
        if not os.path.exists(db_dir):
            os.makedirs(db_dir, exist_ok=True)
            logging_helper.info_if_verbose(self.logger, self.verbose, f"创建数据库目录: {db_dir}")
        
        self.db_path = db_path
        self._init_database()
    
    def reinit_DB(self):
        """重新初始化数据库：删除原有表结构，重新创建表、重新初始化数据"""
        logging_helper.info_if_verbose(self.logger, self.verbose, "开始重新初始化数据库...")
        
        conn = sqlite3.connect(self.db_path)
        cursor = conn.cursor()
        
        try:
            # 获取所有用户表
            cursor.execute("SELECT name FROM sqlite_master WHERE type='table' AND name NOT LIKE 'sqlite_%'")
            tables = [row[0] for row in cursor.fetchall()]
            
            # 删除所有表
            for table in tables:
                cursor.execute(f"DROP TABLE IF EXISTS {table}")
                logging_helper.info_if_verbose(self.logger, self.verbose, f"删除表: {table}")
            
            conn.commit()
            logging_helper.info_if_verbose(self.logger, self.verbose, "所有表删除完成")
            
            # 重新初始化数据库
            self._init_database()
            
            # 重新创建模拟数据
            self.create_sample_data()
            
            logging_helper.info_if_verbose(self.logger, self.verbose, "数据库重新初始化完成")
            
        except Exception as e:
            conn.rollback()
            self.logger.error(f"重新初始化数据库失败: {e}")
            raise
        finally:
            conn.close()
    
    def reload_data(self):
        """重新加载数据：删除各表数据，重新初始化数据，但不删除表结构"""
        logging_helper.info_if_verbose(self.logger, self.verbose, "开始重新加载数据...")
        
        conn = sqlite3.connect(self.db_path)
        cursor = conn.cursor()
        
        try:
            # 获取所有用户表（按依赖顺序排序，避免外键约束冲突）
            tables_order = ['projects_products', 'projects', 'pipline', 'products', 'employees']
            
            # 禁用外键约束
            cursor.execute("PRAGMA foreign_keys = OFF")
            
            # 按顺序清空表数据
            for table in tables_order:
                cursor.execute(f"DELETE FROM {table}")
                logging_helper.info_if_verbose(self.logger, self.verbose, f"清空表数据: {table}")
            
            # 重新启用外键约束
            cursor.execute("PRAGMA foreign_keys = ON")
            
            conn.commit()
            
            # 重新创建模拟数据
            self.create_sample_data()
            
            logging_helper.info_if_verbose(self.logger, self.verbose, "数据重新加载完成")
            
        except Exception as e:
            conn.rollback()
            self.logger.error(f"重新加载数据失败: {e}")
            raise
        finally:
            conn.close()
    
    def getDBSchema(self, output_dir=None):
        """生成数据库schema并存储到指定目录"""
        if output_dir is None:
            current_dir = os.path.dirname(os.path.abspath(__file__))
            kb_root = os.path.dirname(os.path.dirname(current_dir))
            output_dir = os.path.join(kb_root, "database")
        
        # 确保输出目录存在
        if not os.path.exists(output_dir):
            os.makedirs(output_dir, exist_ok=True)
        
        # 获取schema信息
        schema = self.get_database_schema()
        
        # 生成文件名（带时间戳）
        timestamp = datetime.now().strftime("%Y%m%d_%H%M%S")
        
        # 1. 保存JSON格式（用于程序处理）
        schema_json_file = os.path.join(output_dir, f"database_schema_{timestamp}.json")
        with open(schema_json_file, 'w', encoding='utf-8') as f:
            json.dump(schema, f, ensure_ascii=False, indent=2)
        
        # 同时生成一个不带时间戳的最新JSON版本
        latest_json_file = os.path.join(output_dir, "database_schema_latest.json")
        with open(latest_json_file, 'w', encoding='utf-8') as f:
            json.dump(schema, f, ensure_ascii=False, indent=2)
        
        # 2. 生成文本格式（用于向量化和LLM理解）
        schema_text = self._generate_schema_text(schema)
        schema_text_file = os.path.join(output_dir, f"database_schema_{timestamp}.md")
        with open(schema_text_file, 'w', encoding='utf-8') as f:
            f.write(schema_text)
        
        # 同时生成一个不带时间戳的最新文本版本
        latest_text_file = os.path.join(output_dir, "database_schema_latest.md")
        with open(latest_text_file, 'w', encoding='utf-8') as f:
            f.write(schema_text)
        
        logging_helper.info_if_verbose(self.logger, self.verbose, f"数据库schema已保存:")
        logging_helper.info_if_verbose(self.logger, self.verbose, f"  JSON格式: {schema_json_file}")
        logging_helper.info_if_verbose(self.logger, self.verbose, f"  JSON最新: {latest_json_file}")
        logging_helper.info_if_verbose(self.logger, self.verbose, f"  文本格式: {schema_text_file}")
        logging_helper.info_if_verbose(self.logger, self.verbose, f"  文本最新: {latest_text_file}")
        
        return schema_json_file, latest_json_file, schema_text_file, latest_text_file

    def _generate_schema_text(self, schema) -> str:
        """生成便于LLM理解的Markdown格式schema，包含示例数据"""
        lines = []
        
        # 添加头部信息
        metadata = schema.get('metadata', {})
        lines.append("# 企业知识库数据库结构说明")
        lines.append("")
        lines.append(f"- **生成时间**: {metadata.get('generated_time', '未知')}")
        lines.append(f"- **数据库路径**: {metadata.get('database_path', '未知')}")
        lines.append(f"- **表数量**: {metadata.get('table_count', 0)}")
        lines.append(f"- **总字段数**: {metadata.get('total_columns', 0)}")
        lines.append("")
        
        # 获取一些示例数据用于增强理解
        example_data = self._get_example_data()
        
        # 为每个表生成独立的Markdown块
        for table in schema['tables']:
            lines.append("---")
            lines.append(f"## 📊 表: `{table['name']}`")
            lines.append("")
            lines.append(f"**描述**: {table['description']}")
            lines.append("")
            lines.append(f"**业务含义**: {table.get('business_meaning', table['description'])}")
            lines.append("")
            
            # 生成字段表格
            lines.append("### 字段结构")
            lines.append("")
            lines.append("| 字段名 | 类型 | 描述 | 主键 | 可空 | 默认值 |")
            lines.append("|--------|------|------|------|------|--------|")
            
            for column in table['columns']:
                pk = "✅" if column.get('pk', 0) == 1 else ""
                nullable = "❌" if column.get('notnull', 0) == 1 else "✅"
                default_value = column['dflt_value'] if column['dflt_value'] is not None else ""
                
                lines.append(f"| `{column['name']}` | `{column['type']}` | {column.get('description', '')} | {pk} | {nullable} | {default_value} |")
            
            lines.append("")
            
            # 添加表关系（基于schema中的relationships数据）
            table_relationships = self._get_relationships_for_table(schema['relationships'], table['name'])
            if table_relationships:
                lines.append("### 表关系")
                lines.append("")
                for rel in table_relationships:
                    if rel['from_table'] == table['name']:
                        lines.append(f"- **外键关系**: `{rel['from_table']}.{rel['from_column']}` → `{rel['to_table']}.{rel['to_column']}`")
                        lines.append(f"  - 类型: {rel['relationship_type']}")
                        lines.append(f"  - 描述: {rel['description']}")
                    else:
                        lines.append(f"- **被引用关系**: `{rel['from_table']}.{rel['from_column']}` → `{table['name']}.{rel['to_column']}`")
                        lines.append(f"  - 类型: {rel['relationship_type']}")
                        lines.append(f"  - 描述: {rel['description']}")
                lines.append("")
            
            # 添加示例数据
            if table['name'] in example_data and example_data[table['name']]:
                lines.append("### 示例数据")
                lines.append("")
                sample_record = example_data[table['name']][0]  # 取第一条记录作为示例
                lines.append("```json")
                lines.append(json.dumps(sample_record, ensure_ascii=False, indent=2))
                lines.append("```")
                lines.append("")
            
            # 动态生成使用提示（完全基于字段信息）
            lines.append("### 使用提示")
            lines.append("")
            
            # 基于字段类型和描述生成提示
            usage_hints = self._generate_usage_hints(table['columns'], table_relationships)
            for hint in usage_hints:
                lines.append(f"- {hint}")
            lines.append("")
            
            # 添加分隔标识，确保ChromaDB分块时不会拆分
            lines.append("<!-- TABLE_END -->")
            lines.append("")
        
        # 添加全局关系图（基于relationships数据）
        if schema['relationships']:
            lines.append("---")
            lines.append("## 🔗 全局表关系图")
            lines.append("")
            lines.append("```")
            # 基于实际的关系数据生成关系图
            for rel in schema['relationships']:
                lines.append(f"{rel['from_table']}.{rel['from_column']} → {rel['to_table']}.{rel['to_column']}")
            lines.append("```")
            lines.append("")
        
        return "\n".join(lines)

    def _get_relationships_for_table(self, relationships, table_name):
        """获取与指定表相关的所有关系"""
        return [rel for rel in relationships 
                if rel['from_table'] == table_name or rel['to_table'] == table_name]

    def _generate_usage_hints(self, columns, relationships):
        """基于字段信息和关系动态生成使用提示"""
        hints = []
        
        # 分析字段特征生成提示
        column_names = [col['name'].lower() for col in columns]
        column_descriptions = [col.get('description', '').lower() for col in columns]
        
        # 基于字段名和描述推断用途
        for col in columns:
            col_name = col['name'].lower()
            col_desc = col.get('description', '').lower()
            
            # 识别ID字段
            if col_name.endswith('_id') or '编号' in col_desc or 'id' in col_name:
                if col.get('pk', 0) == 1:
                    hints.append(f"`{col['name']}` 是主键，用于唯一标识记录")
                else:
                    hints.append(f"`{col['name']}` 是外键，用于关联其他表")
            
            # 识别名称字段
            elif col_name.endswith('_name') or '名称' in col_desc:
                hints.append(f"`{col['name']}` 可用于筛选和搜索")
            
            # 识别金额字段
            elif 'amount' in col_name or '金额' in col_desc or col['type'].upper() == 'REAL':
                hints.append(f"`{col['name']}` 可用于统计分析和金额计算")
            
            # 识别日期字段
            elif 'date' in col_name or '日期' in col_desc:
                hints.append(f"`{col['name']}` 可用于时间范围查询和趋势分析")
            
            # 识别状态字段
            elif 'status' in col_name or '状态' in col_desc:
                hints.append(f"`{col['name']}` 可用于按状态筛选和统计")
        
        # 基于关系生成提示
        for rel in relationships:
            hints.append(f"可通过 `{rel['from_column']}` 关联 `{rel['to_table']}` 表")
        
        # 去重并限制数量
        unique_hints = list(dict.fromkeys(hints))  # 保持顺序的去重
        return unique_hints[:8]  # 返回前8个提示，避免过多

    def _get_example_data(self):
        """从数据库获取各表的示例数据"""
        example_data = {}
        
        try:
            # 为每个表获取1条示例数据
            tables = ['employees', 'products', 'pipline', 'projects', 'projects_products']
            
            for table in tables:
                query = f"SELECT * FROM {table} LIMIT 1"
                results = self.execute_query(query)
                if results:
                    example_data[table] = results
                    
        except Exception as e:
            self.logger.warning(f"获取示例数据失败: {e}")
        
        return example_data

    def _init_database(self):
        """初始化数据库表结构 - 使用预定义的描述信息"""
        conn = sqlite3.connect(self.db_path)
        cursor = conn.cursor()
        
        # 启用外键约束
        cursor.execute("PRAGMA foreign_keys = ON")
        
        # 创建员工表
        cursor.execute(f'''CREATE TABLE IF NOT EXISTS employees (
                -- {self.table_descriptions['employees']}
                employee_id TEXT PRIMARY KEY,        -- 员工唯一编号
                name TEXT NOT NULL,                  -- 员工姓名
                phone TEXT,                          -- 联系电话
                department TEXT NOT NULL,            -- 所属部门
                position TEXT NOT NULL CHECK(position IN ('总经理', '销售经理', '销售员', '项目经理', '开发工程师'))
                -- 职位：总经理、销售经理、销售员、项目经理、开发工程师
            )''')
        
        # 创建产品表
        cursor.execute(f'''CREATE TABLE IF NOT EXISTS products (
                -- {self.table_descriptions['products']}
                product_id TEXT PRIMARY KEY,         -- 产品唯一编号
                product_name TEXT NOT NULL,          -- 产品名称
                product_description TEXT NOT NULL,   -- 产品功能描述
                product_owner TEXT NOT NULL          -- 产品负责人
            )''')
        
        # 创建商机表 (pipline)
        cursor.execute(f'''CREATE TABLE IF NOT EXISTS pipline (
                -- {self.table_descriptions['pipline']}
                opportunity_id TEXT PRIMARY KEY,     -- 商机唯一编号
                opportunity_name TEXT NOT NULL,      -- 商机业务名称
                customer TEXT NOT NULL,              -- 客户公司名称
                region TEXT NOT NULL,                -- 业务所属地理大区
                products TEXT NOT NULL,              -- 涉及的产品服务列表
                estimated_amount REAL NOT NULL,      -- 商机预计金额(元)
                lead_sales TEXT NOT NULL,            -- 主导销售人员
                start_date TEXT NOT NULL,            -- 商机开始日期
                expected_close_date TEXT NOT NULL,   -- 预计结单日期
                status TEXT NOT NULL CHECK(status IN ('跟进中', '已赢单', '已弃单', '已丢单'))
                -- 商机当前状态：跟进中、已赢单、已弃单、已丢单
            )''')
        
        # 创建项目表 (projects)
        cursor.execute(f'''CREATE TABLE IF NOT EXISTS projects (
                -- {self.table_descriptions['projects']}
                project_id TEXT PRIMARY KEY,         -- 项目唯一编号
                project_name TEXT NOT NULL,          -- 项目名称
                opportunity_id TEXT,                 -- 关联商机编号（外键，关联pipline表）
                contract_amount REAL NOT NULL,       -- 项目合同金额(元)
                account_manager TEXT NOT NULL,       -- 客户经理
                project_manager TEXT NOT NULL,       -- 项目经理
                status TEXT NOT NULL CHECK(status IN ('工作中', '挂起', '实施结项')),
                -- 项目执行状态：工作中、挂起、实施结项
                start_date TEXT NOT NULL,            -- 项目开始日期
                expected_end_date TEXT NOT NULL,     -- 预计结束日期
                actual_end_date TEXT,                -- 实际结项日期
                FOREIGN KEY (opportunity_id) REFERENCES pipline (opportunity_id)
            )''')
        
        # 创建项目产品表 (projects_products)
        cursor.execute(f'''CREATE TABLE IF NOT EXISTS projects_products (
                -- {self.table_descriptions['projects_products']}
                id INTEGER PRIMARY KEY AUTOINCREMENT, -- 自增主键
                project_id TEXT NOT NULL,            -- 关联项目编号（外键，关联projects表）
                product_id TEXT NOT NULL,            -- 产品编号（外键，关联products表）
                product_description TEXT NOT NULL,   -- 产品需求描述
                product_sales_amount REAL NOT NULL,  -- 产品销售金额(元)
                product_implementation_cost REAL NOT NULL, -- 产品实施成本(元)
                product_owner TEXT NOT NULL,         -- 产品负责人
                FOREIGN KEY (project_id) REFERENCES projects (project_id),
                FOREIGN KEY (product_id) REFERENCES products (product_id)
            )''')
        
        conn.commit()
        conn.close()
        logging_helper.info_if_verbose(self.logger, self.verbose, f"数据库初始化完成: {self.db_path}")
    
    def get_database_schema(self) -> Dict:
        """获取完整的数据库schema信息"""
        conn = sqlite3.connect(self.db_path)
        conn.row_factory = sqlite3.Row
        cursor = conn.cursor()
        
        schema = {
            'tables': [],
            'relationships': self._get_table_relationships(),
            'metadata': {
                'generated_time': datetime.now().isoformat(),
                'database_path': self.db_path,
                'table_count': 0,
                'total_columns': 0
            }
        }
        
        # 获取所有用户表信息
        cursor.execute("SELECT name FROM sqlite_master WHERE type='table' AND name NOT LIKE 'sqlite_%'")
        tables = [row['name'] for row in cursor.fetchall()]
        
        for table in tables:
            # 获取表结构信息
            cursor.execute(f"PRAGMA table_info({table})")
            columns = []
            
            for row in cursor.fetchall():
                col_info = dict(row)
                # 获取字段的注释信息
                col_info['description'] = self._get_column_comment(table, col_info['name'])
                columns.append(col_info)
            
            # 获取表的描述信息
            table_description = self._get_table_comment(table)
            
            schema['tables'].append({
                'name': table,
                'description': table_description,
                'columns': columns,
                'column_count': len(columns)
            })
        
        # 更新元数据
        schema['metadata']['table_count'] = len(schema['tables'])
        schema['metadata']['total_columns'] = sum(table['column_count'] for table in schema['tables'])
        
        conn.close()
        return schema

    def _get_table_comment(self, table_name: str) -> str:
        """获取表的注释信息"""
        conn = sqlite3.connect(self.db_path)
        cursor = conn.cursor()
        
        cursor.execute("SELECT sql FROM sqlite_master WHERE type='table' AND name=?", (table_name,))
        result = cursor.fetchone()
        conn.close()
        
        if result:
            sql_statement = result[0]
            lines = sql_statement.split('\n')
            
            # 查找表的注释（在CREATE TABLE语句的第一行）
            for line in lines:
                line = line.strip()
                if line.startswith('--'):
                    # 提取注释内容，去除 "--" 和前后空格
                    comment = line.strip('--').strip()
                    return comment
        
        # 如果从数据库中没有提取到注释，使用预定义的描述
        return self.table_descriptions.get(table_name, f"{table_name}表")
    
    def _get_column_comment(self, table_name: str, column_name: str) -> str:
        """获取字段的注释信息"""
        conn = sqlite3.connect(self.db_path)
        cursor = conn.cursor()
        
        cursor.execute("SELECT sql FROM sqlite_master WHERE type='table' AND name=?", (table_name,))
        result = cursor.fetchone()
        conn.close()
        
        if result:
            sql_statement = result[0]
            lines = sql_statement.split('\n')
            
            # 查找字段的注释
            for i, line in enumerate(lines):
                line_clean = line.strip()
                # 找到包含字段名的行（不是注释行）
                if column_name in line_clean and not line_clean.startswith('--') and not line_clean.startswith('CREATE'):
                    # 检查前一行是否是注释
                    if i > 0 and lines[i-1].strip().startswith('--'):
                        comment = lines[i-1].strip().strip('--').strip()
                        return comment
                    # 检查同一行末尾是否有注释
                    if '--' in line_clean:
                        parts = line_clean.split('--', 1)
                        if len(parts) > 1:
                            return parts[1].strip()
            
            # 如果没有找到注释，返回字段名
            return column_name
        
        return column_name
    
    def _get_table_relationships(self) -> List[Dict]:
        """通过查询数据库的外键约束来获取表关系"""
        conn = sqlite3.connect(self.db_path)
        cursor = conn.cursor()
        
        relationships = []
        
        cursor.execute("SELECT name FROM sqlite_master WHERE type='table' AND name NOT LIKE 'sqlite_%'")
        tables = [row[0] for row in cursor.fetchall()]
        
        for table in tables:
            cursor.execute(f"PRAGMA foreign_key_list({table})")
            foreign_keys = cursor.fetchall()
            
            for fk in foreign_keys:
                relationship = {
                    'from_table': table,
                    'from_column': fk[3],
                    'to_table': fk[2],
                    'to_column': fk[4],
                    'relationship_type': 'one-to-many',
                    'description': f'{table}表的{fk[3]}字段关联到{fk[2]}表的{fk[4]}字段'
                }
                relationships.append(relationship)
        
        conn.close()
        return relationships

    def create_sample_data(self):
        """创建模拟数据"""
        conn = sqlite3.connect(self.db_path)
        cursor = conn.cursor()
        
        # 清空现有数据（按依赖顺序）
        cursor.execute("PRAGMA foreign_keys = OFF")
        cursor.execute("DELETE FROM projects_products")
        cursor.execute("DELETE FROM projects")
        cursor.execute("DELETE FROM pipline")
        cursor.execute("DELETE FROM products")
        cursor.execute("DELETE FROM employees")
        cursor.execute("PRAGMA foreign_keys = ON")
        
        # 插入员工数据
        employees_data = [
            ('EMP001', '黄俊', '13800138001', '管理层', '总经理'),
            ('EMP002', '范闲', '13800138002', '销售部', '销售经理'),
            ('EMP003', '范思哲', '13800138003', '销售部', '销售员'),
            ('EMP004', '萧炎', '13800138004', '项目部', '项目经理'),
            ('EMP005', '王林', '13800138005', '项目部', '项目经理'),
            ('EMP006', '韩李', '13800138006', '项目部', '项目经理'),
            ('EMP007', '魏芳', '13800138007', '技术部', '开发工程师'),
            ('EMP008', '黄睿', '13800138008', '技术部', '开发工程师'),
            ('EMP009', '李双', '13800138009', '技术部', '开发工程师')
        ]
        
        cursor.executemany('INSERT INTO employees VALUES (?, ?, ?, ?, ?)', employees_data)
        
        # 插入产品数据
        products_data = [
            ('PROD001', '企业级知识库引擎', '面向企业的大规模知识管理和智能检索系统，支持多模态数据接入和智能问答', '黄睿'),
            ('PROD002', 'AI问答机器人', '基于大语言模型的智能客服机器人，支持多轮对话和业务场景定制', '魏芳'),
            ('PROD003', 'AI数字人', '逼真的虚拟数字人，支持语音交互、表情动作和个性化形象定制', '李双')
        ]
        
        cursor.executemany('INSERT INTO products VALUES (?, ?, ?, ?)', products_data)
        
        # 插入商机数据
        pipline_data = [
            ('OPP2024001', '智慧城市AI平台建设', '北京市政府', '华北', 'PROD001,PROD002', 5000000.0, '范思哲', '2024-01-15', '2024-06-30', '已赢单'),
            ('OPP2024002', '制造业数字化转型项目', '上海汽车集团', '华东', 'PROD001,PROD003', 3000000.0, '范思哲', '2024-02-10', '2024-08-15', '已赢单'),
            ('OPP2024003', '金融风控系统升级', '深圳发展银行', '华南', 'PROD001', 2000000.0, '范思哲', '2024-03-20', '2024-09-30', '已弃单'),
            ('OPP2024004', '教育智能化平台开发', '清华大学', '华北', 'PROD002', 1500000.0, '范思哲', '2024-04-05', '2024-10-20', '已丢单'),
            ('OPP2025001', '医疗AI诊断系统建设', '协和医院', '华北', 'PROD001,PROD002,PROD003', 8000000.0, '范思哲', '2025-01-10', '2025-12-31', '跟进中'),
            ('OPP2025002', '零售智能分析平台', '阿里巴巴集团', '华东', 'PROD001,PROD002', 4000000.0, '范思哲', '2025-02-15', '2025-11-30', '跟进中'),
            ('OPP2025003', '能源管理系统智能化', '国家电网', '华北', 'PROD001', 6000000.0, '范思哲', '2025-03-01', '2025-10-15', '跟进中'),
            ('OPP2025004', '智慧交通指挥平台', '广州市政府', '华南', 'PROD001,PROD003', 3500000.0, '范思哲', '2025-03-20', '2025-12-20', '跟进中'),
            ('OPP2025005', '农业物联网监测系统', '新希望集团', '西南', 'PROD002,PROD003', 2500000.0, '范思哲', '2025-04-10', '2025-11-15', '跟进中')
        ]
        
        cursor.executemany('INSERT INTO pipline VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)', pipline_data)
        
        # 插入项目数据
        projects_data = [
            ('PROJ2024001', '北京市智慧城市AI平台实施项目', 'OPP2024001', 4800000.0, '范闲', '萧炎', '实施结项', '2024-07-01', '2024-12-31', '2024-12-15'),
            ('PROJ2024002', '上汽集团数字化转型项目', 'OPP2024002', 2800000.0, '范闲', '王林', '实施结项', '2024-09-01', '2025-03-31', '2025-03-20'),
            ('PROJ2024003', '清华大学教育平台开发项目', 'OPP2024004', 0.0, '范闲', '韩李', '挂起', '2024-11-01', '2025-05-31', None),
            ('PROJ2025001', '协和医院AI诊断系统建设项目', 'OPP2025001', 0.0, '范闲', '萧炎', '工作中', '2025-04-01', '2025-12-31', None),
            ('PROJ2025002', '阿里零售智能分析平台项目', 'OPP2025002', 0.0, '范闲', '王林', '工作中', '2025-05-01', '2025-11-30', None),
            ('PROJ2025003', '国家电网能源管理系统项目', 'OPP2025003', 0.0, '范闲', '韩李', '工作中', '2025-06-01', '2025-12-31', None)
        ]
        
        cursor.executemany('INSERT INTO projects VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)', projects_data)
        
        # 插入项目产品数据
        projects_products_data = [
            ('PROJ2024001', 'PROD001', '企业级知识库引擎部署，支持城市数据管理和智能检索', 3000000.0, 800000.0, '黄睿'),
            ('PROJ2024001', 'PROD002', 'AI问答机器人定制开发，用于市民服务咨询', 1800000.0, 500000.0, '魏芳'),
            ('PROJ2024002', 'PROD001', '制造业知识库系统，支持生产流程优化', 1500000.0, 300000.0, '黄睿'),
            ('PROJ2024002', 'PROD003', 'AI数字人客服，用于汽车销售咨询服务', 1300000.0, 400000.0, '李双'),
            ('PROJ2025001', 'PROD001', '医疗知识库引擎，集成医学文献和病例数据', 3500000.0, 900000.0, '黄睿'),
            ('PROJ2025001', 'PROD002', '医疗问答机器人，支持患者咨询和初步诊断', 2500000.0, 600000.0, '魏芳'),
            ('PROJ2025001', 'PROD003', 'AI数字人医生助手，用于患者导诊服务', 2000000.0, 500000.0, '李双'),
            ('PROJ2025002', 'PROD001', '零售知识库系统，支持商品管理和营销策略', 2000000.0, 500000.0, '黄睿'),
            ('PROJ2025002', 'PROD002', '智能客服机器人，处理用户咨询和订单问题', 2000000.0, 400000.0, '魏芳'),
            ('PROJ2025003', 'PROD001', '能源管理知识库，集成电网运行数据和维护手册', 6000000.0, 1500000.0, '黄睿')
        ]
        
        cursor.executemany('''
            INSERT INTO projects_products (project_id, product_id, product_description, product_sales_amount, product_implementation_cost, product_owner)
            VALUES (?, ?, ?, ?, ?, ?)
        ''', projects_products_data)
        
        conn.commit()
        conn.close()
        logging_helper.info_if_verbose(self.logger, self.verbose, "模拟数据创建完成")
    
    def execute_query(self, query: str, params: tuple = ()) -> List[Dict]:
        """执行SQL查询并返回结果"""
        conn = sqlite3.connect(self.db_path)
        conn.row_factory = sqlite3.Row
        cursor = conn.cursor()
        
        try:
            cursor.execute(query, params)
            results = [dict(row) for row in cursor.fetchall()]
            return results
        except Exception as e:
            self.logger.error(f"数据库查询失败: {e}")
            return []
        finally:
            conn.close()
    
    def get_employee_info(self, employee_name: str) -> Optional[Dict]:
        """获取员工信息"""
        query = "SELECT * FROM employees WHERE name = ?"
        results = self.execute_query(query, (employee_name,))
        return results[0] if results else None
    
    def get_product_info(self, product_id: str) -> Optional[Dict]:
        """获取产品信息"""
        query = "SELECT * FROM products WHERE product_id = ?"
        results = self.execute_query(query, (product_id,))
        return results[0] if results else None
    
    def get_project_by_manager(self, manager_name: str) -> List[Dict]:
        """获取项目经理负责的项目"""
        query = """
            SELECT p.*, e.department, e.position 
            FROM projects p 
            LEFT JOIN employees e ON p.project_manager = e.name 
            WHERE p.project_manager = ?
            ORDER BY p.start_date DESC
        """
        return self.execute_query(query, (manager_name,))
    
    def get_pipline_summary_by_year(self, year: str = None) -> Dict:
        """获取商机年度汇总"""
        if year:
            query = """
                SELECT status, COUNT(*) as count, SUM(estimated_amount) as total_amount 
                FROM pipline 
                WHERE substr(start_date, 1, 4) = ?
                GROUP BY status
            """
            results = self.execute_query(query, (year,))
        else:
            query = """
                SELECT status, COUNT(*) as count, SUM(estimated_amount) as total_amount 
                FROM pipline 
                GROUP BY status
            """
            results = self.execute_query(query)
        
        return {
            'year': year,
            'results': results,
            'total_count': sum(item['count'] for item in results),
            'total_amount': sum(item['total_amount'] or 0 for item in results)
        }
    
    def get_project_summary_by_year(self, year: str = None) -> Dict:
        """获取项目年度汇总"""
        if year:
            query = """
                SELECT status, COUNT(*) as count, SUM(contract_amount) as total_amount 
                FROM projects 
                WHERE substr(start_date, 1, 4) = ?
                GROUP BY status
            """
            results = self.execute_query(query, (year,))
        else:
            query = """
                SELECT status, COUNT(*) as count, SUM(contract_amount) as total_amount 
                FROM projects 
                GROUP BY status
            """
            results = self.execute_query(query)
        
        return {
            'year': year,
            'results': results,
            'total_count': sum(item['count'] for item in results),
            'total_amount': sum(item['total_amount'] or 0 for item in results)
        }